/**
 * created on 2012-04-25
 * @author  qiujz
 * @version 1.0
 *
 * Copyright (c)  Founder Software (Suzhou) Co., Ltd.  2012
 *  All rights reserved.
 *
 * 
 * ================================================================
 * Change Revision
 * ---------------------------------------------------------------
 *    DATE                BY                   COMMENT
 * ---------------------------------------------------------------
 * 2012-04-25         qiujz                Create class
 * 
 *
 * ================================================================
 */

package com.qjz.service.impl;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.jws.WebService;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.founder.mh.framework.entity.QueryResultBase;
import com.founder.mh.framework.entity.Sort;
import com.founder.mh.framework.service.impl.BaseServiceImpl;
import com.founder.mh.framework.util.BuildTableUtil;
import com.qjz.dao.IStudentInfoDao;
import com.qjz.entity.DataQueryParameter;
import com.qjz.entity.DistinctTo;
import com.qjz.entity.StudentInfoQueryParameter;
import com.qjz.entity.StudentInfoTo;
import com.qjz.service.IStudentInfoService;
import com.qjz.util.ExcelUtil;
import com.qjz.util.FileOperate;

@WebService(endpointInterface = "com.qjz.service.IStudentInfoService")
public class StudentInfoServiceImpl extends BaseServiceImpl implements
		IStudentInfoService {

	private static final Log LOGGER = LogFactory
			.getLog(StudentInfoServiceImpl.class);

	private IStudentInfoDao studentInfoDao;

	/**
	 * @return Dao接口
	 */
	public IStudentInfoDao getStudentInfoDao() {
		return studentInfoDao;
	}

	/**
	 * @param studentInfoDao
	 *            Dao接口
	 */
	public void setStudentInfoDao(IStudentInfoDao studentInfoDao) {
		this.studentInfoDao = studentInfoDao;
	}

	/**
	 * @inheritDoc
	 * @see com.qjz.service.IStudentInfoService
	 *      #searchStudentInfoList(com.qjz.entity.StudentInfoQueryParameter)
	 */
	public QueryResultBase searchStudentInfoList(
			StudentInfoQueryParameter paramter) {
		if (paramter == null) {
			throw new RuntimeException("查询参数对象不能为空!");
		}

		// 放置查询结果的对象
		QueryResultBase rtnResult = new QueryResultBase();

		// 设置查询参数
		Map<String, Object> paramMap = getMapParamter(paramter);

		List<StudentInfoTo> studentInfoList = null;
		int total = studentInfoDao.getStudentInfoCount(paramMap);
		rtnResult.setTotal(total);
		if (total == 0) {
			return rtnResult;
		}

		// 设置排序字段
		List<Sort> sortList = paramter.getSort();
		if (sortList != null && sortList.size() > 0) {
			StringBuilder sb = new StringBuilder();
			for (Sort sort : sortList) {
				sb.append(sort.getColumnName()).append(" ")
						.append(sort.getOrder()).append(",");
			}

			paramMap.put("sort", sb.toString()
					.substring(0, sb.lastIndexOf(",")));

			if (LOGGER.isDebugEnabled()) {
				LOGGER.debug(paramMap.get("sort"));
			}
		}

		// 不分页的情况
		if (paramter.getStartRow() <= 0) {
			studentInfoList = studentInfoDao.searchStudentInfoList(paramMap);
		} else {
			// 执行分页询
			int startRow = paramter.getStartRow() - 1;
			paramMap.put("pageSize", paramter.getPageSize());
			paramMap.put("startRow", startRow);
			studentInfoList = studentInfoDao.searchStudentInfoPagination(paramMap);
		}
		rtnResult.setPageData(studentInfoList);
		return rtnResult;
	}

	/**
	 * @inheritDoc
	 * @see com.qjz.service.IStudentInfoService
	 *      #getStudentInfoByPrimaryKey(String)
	 */
	public StudentInfoTo getStudentInfoByPrimaryKey(int sfid) {
		return studentInfoDao.getStudentInfoByPrimaryKey(sfid);
	}

	/**
	 * @inheritDoc
	 * @see com.qjz.service.IStudentInfoService
	 *      #insertStudentInfo(com.qjz.entityStudentInfo.StudentInfoTo)
	 */
	public StudentInfoTo insertStudentInfo(StudentInfoTo studentInfo) {

		studentInfoDao.insertStudentInfo(studentInfo);
		// studentInfo = studentInfoDao.getStudentInfoByPrimaryKey(sfid);

		return studentInfo;
	}

	/**
	 * @inheritDoc
	 * @see com.qjz.service.IStudentInfoService
	 *      #updateStudentInfo(com.qjz.entity.StudentInfoTo)
	 */
	public int updateStudentInfo(StudentInfoTo studentInfo) {
		return studentInfoDao.updateStudentInfoByBean(studentInfo);
	}

	/**
	 * @inheritDoc
	 * @see com.qjz.service.IStudentInfoService #deleteStudentInfo(String)
	 */
	public int deleteStudentInfo(int sfid) {
		return studentInfoDao.deleteStudentInfoByPrimaryKey(sfid);
	}

	/**
	 * @inheritDoc
	 * @see com.qjz.service.IStudentInfoService
	 *      #deleteStudentInfoBatch(String[])
	 */
	public void deleteStudentInfoBatch(String[] keyArray) {
		studentInfoDao.deleteStudentInfoBatch(keyArray);
	}

	@Override
	public void synData(StudentInfoQueryParameter paramter,
			List<StudentInfoTo> list) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("school", paramter.getSchool());
		String shenFenZhengs = "";
		for (StudentInfoTo student : list) {
			if (null != student.getShenFenZheng()
					&& !"".equals(student.getShenFenZheng()))
				shenFenZhengs += "'" + student.getShenFenZheng() + "',";
		}
		if (!"".equals(shenFenZhengs))
		{
			shenFenZhengs=shenFenZhengs.substring(0, shenFenZhengs.length() - 1);
			paramMap.put("shenFenZhengs", shenFenZhengs);
		}
		
		if (paramter.getiDelete() 
				&& paramter.getUpdate()
				&& paramter.getInsert()) {
			paramMap.remove("shenFenZhengs");
			paramMap.put("pType", "1");
			studentInfoDao.deleteStudentInfoByParamter(paramMap);
			studentInfoDao.insertStudentInfoBatch(list);
		} else {
			if (paramter.getiDelete()) {
				// 系统中有、EXCEL表中没有的记录，删除系统中的记录
				paramMap.put("pType", "1");
				studentInfoDao.deleteStudentInfoByParamter(paramMap);
			}
			if (paramter.getUpdate()) {
				// 数据库和EXCEL表中同时有的记录，更新数据库中的记录
				paramMap.put("pType", "2");
				studentInfoDao.deleteStudentInfoByParamter(paramMap);
			}else{
				// 数据库和EXCEL表中同时有的记录，不更新
				if (!"".equals(shenFenZhengs))
				{
					Map<String, Object> paramMap2 = new HashMap<String, Object>();
					paramMap2.put("xueXiaoDaiMa", paramter.getSchool());
					paramMap2.put("shenFenZhengs", shenFenZhengs);
					List<StudentInfoTo> list2=studentInfoDao.searchStudentInfoList(paramMap2);
					for (StudentInfoTo student2 : list2) {
						for (StudentInfoTo student : list) {
							if(student.getXingMing().equals(student2.getXingMing())
									&& student.getShenFenZheng().equals(student2.getShenFenZheng()) ){
								list.remove(student);
								if(list == null || list.isEmpty())
									return;
							}
						}
					}
				}				
			}
			
			if (paramter.getInsert()) {
				// 系统中没有、EXCEL表中有的记录，添加到数据库中
				
			}
			
			studentInfoDao.insertStudentInfoBatch(list);
		}
	}

	@Override
	public String exportData(StudentInfoQueryParameter paramter) {
		Map<String, Object> paramMap = getMapParamter(paramter);
		List<StudentInfoTo> studentInfoList = studentInfoDao.searchStudentInfoList(paramMap);		
		return ExcelUtil.saveGeneratedExcel(ExcelUtil.studentDataExcel(studentInfoList), 12L, "学生信息导出");
	}
	
	public List<DistinctTo> searchDistinctList(){
		return studentInfoDao.searchDistinctList();
	}
	
	@Override
	public List<DistinctTo> searchBanJiList(String school) {
		return studentInfoDao.searchBanJiList(school);
	}
	
	private Map<String, Object> getMapParamter(StudentInfoQueryParameter paramter){
		// 设置查询参数
		Map<String, Object> paramMap = new HashMap<String, Object>();
		// ToDO Add parameter
		StudentInfoTo student=paramter.getStudentInfo();
		paramMap.put("studentIds", paramter.getStudentIds());
		if(null != student){
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.IS_DELETE),
					student.getIsDelete());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.XING_MING),
					student.getXingMing());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.XUE_JI_HAO),
					student.getXueJiHao());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.SHEN_FEN_ZHENG),
					student.getShenFenZheng());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.XUE_JI_ZHUANG_TAI),
					student.getXueJiZhuangTai());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.RU_XUE_NIAN),
					student.getRuXueNian());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.MIN_ZU),
					student.getMinZu());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.GUO_JI),
					student.getGuoJi());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.QIAO_BAO),
					student.getQiaoBao());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.XING_BIE),
					student.getXingBie());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.YOU_WU_SFZ),
					student.getYouWuSfz());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.CAN_JI),
					student.getCanJi());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.LIU_SHOU),
					student.getLiuShou());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.GU_ER),
					student.getGuEr());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.JI_SU),
					student.getJiSu());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.XUE_XIAO_DAI_MA),
					student.getXueXiaoDaiMa());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.BAN_JI),
					student.getBanJi());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.SHENG_YUAN_XING_ZHI),
					student.getShengYuanXingZhi());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.DI_BAO_HU),
					student.getDiBaoHu());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.TE_SHU_BING_LI),
					student.getTeShuBingLi());			
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.HU_JI_DI_ZHI),
					student.getHuJiDiZhi());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.JIA_TING_ZHU_ZHI),
					student.getJiaTingZhuZhi());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.SHI_MIN_KA),
					student.getShiMinKa());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.DI_BAO_ZHENG),
					student.getDiBaoZheng());
			paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.BING_LI_MING),
					student.getBingLiMing());
			if(student.getRuXueNianLing() !=0)
				paramMap.put(BuildTableUtil.
					buildPropertyNameFromColumn(StudentInfoTo.RU_XUE_NIAN_LING),
					student.getRuXueNianLing());
		}
		paramMap.put("TJMinZu", paramter.getTJMinZu());
		paramMap.put("TJGuoJi", paramter.getTJGuoJi());
		paramMap.put("TJQiaoBao", paramter.getTJQiaoBao());
		paramMap.put("xueDuan", paramter.getXueDuan());
		if(paramter.getXinXiaoId() !=0)
			paramMap.put("xinXiaoId", paramter.getXinXiaoId());
		
		return paramMap;
	}
	
	@Override
	public List<StudentInfoTo> getExcelData(DataQueryParameter paramter) {
		List<StudentInfoTo> list = new ArrayList<StudentInfoTo>();
		//String xueJis = "";
		String uploadDir = FileOperate.getDir("upload");
		String path = uploadDir + "\\upload\\";
		try {
			InputStream is = new FileInputStream(path + paramter.getFileName());
			Workbook wb = Workbook.getWorkbook(is);
			Sheet sheet = wb.getSheet(0);
			if (sheet != null) {
				int rowNum = sheet.getRows();
				int colNum = sheet.getColumns();
				for (int i = 0; i < rowNum; i++) {
					if (i != 0) {
						StudentInfoTo student = new StudentInfoTo();
						student.setXueXiaoDaiMa(paramter.getSchoolCode());
						student.setXueXiaoLeiXing(paramter.getSchoolType());
						for (int j = 0; j < colNum; j++) {
							Cell cell = sheet.getCell(j, i);
							Cell title = sheet.getCell(j, 0);
							if (title.getContents().equals("姓名")
									|| title.getContents().equals("学生姓名")) {
								student.setXingMing(cell.getContents());
								continue;
							}
							if (title.getContents().equals("曾用名")) {
								student.setCengYongMing(cell.getContents());
								continue;
							}
							if (title.getContents().equals("性别")) {
								student.setXingBie(cell.getContents());
								continue;
							}
							if (title.getContents().equals("民族")) {
								student.setMinZu(cell.getContents());
								continue;
							}
							if (title.getContents().equals("入学年份")
									|| title.getContents().equals("入园年月")) {
								student.setRuXueNian(cell.getContents());
								continue;
							}
							if (title.getContents().equals("班级")) {
								student.setBanJi(cell.getContents());
								continue;
							}
							if (title.getContents().equals("学校")
									|| title.getContents().equals("所在幼儿园")) {
								student.setXueXiao(cell.getContents());
								continue;
							}
							if (title.getContents().equals("学籍号")) {
								student.setXueJiHao(cell.getContents());
								continue;
							}
							if (title.getContents().equals("身份证号")) {
								student.setShenFenZheng(cell.getContents());
								if(!student.getShenFenZheng().equals("")){
									student.setYouWuSfz("是");
								}else{
									student.setYouWuSfz("");
								}
								continue;
							}
							if (title.getContents().equals("国籍")) {
								student.setGuoJi(cell.getContents());
								continue;
							}
							if (title.getContents().equals("港澳台侨胞")
									|| title.getContents().equals("外籍或港澳台")) {
								student.setQiaoBao(cell.getContents());
								continue;
							}
							if (title.getContents().equals("户籍地址")
									|| title.getContents().equals("户籍所在地址")
									|| title.getContents().equals("户籍所在地")) {
								student.setHuJiDiZhi(cell.getContents());
								continue;
							}
							if (title.getContents().equals("家庭常住地区")
									|| title.getContents().equals("家庭常住地")) {
								student.setChangZhuDiQu(cell.getContents());
								continue;
							}
							if (title.getContents().equals("家庭常住地详细地址")) {
								student.setXiangXiDiZhi(cell.getContents());
								continue;
							}
							if (title.getContents().equals("入学方式")) {
								student.setRuXueFangShi(cell.getContents());
								continue;
							}
							if (title.getContents().equals("中考成绩")) {
								student.setChengJi(cell.getContents());
								continue;
							}
							if (title.getContents().equals("学习简历")) {
								student.setXueXiJianLi(cell.getContents());
								continue;
							}
							if (title.getContents().equals("学籍状态")
									|| title.getContents().equals("状态")) {
								student.setXueJiZhuangTai(cell.getContents());
								continue;
							}
							if (title.getContents().equals("市民卡号")) {
								student.setShiMinKa(cell.getContents());
								continue;
							}
							if (title.getContents().equals("所在派出所")
									|| title.getContents().equals("户口所在派出所")) {
								student.setPaiChuSuo(cell.getContents());
								continue;
							}
							if (title.getContents().equals("家庭住址")) {
								student.setJiaTingZhuZhi(cell.getContents());
								continue;
							}
							if (title.getContents().equals("生源性质")) {
								student.setShengYuanXingZhi(cell.getContents());
								continue;
							}
							if (title.getContents().equals("中考证号")) {
								student.setZhongKaoZheng(cell.getContents());
								continue;
							}
							if (title.getContents().equals("是否低保户或低保边缘户")) {
								student.setDiBaoHu(cell.getContents());
								continue;
							}
							if (title.getContents().equals("低保户或低保边缘户证件号")) {
								student.setDiBaoZheng(cell.getContents());
								continue;
							}
							if (title.getContents().equals("是否特殊病例学生")) {
								student.setTeShuBingLi(cell.getContents());
								continue;
							}
							if (title.getContents().equals("特殊病例名称")) {
								student.setBingLiMing(cell.getContents());
								continue;
							}
							if (title.getContents().equals("第一监护人称谓")) {
								student.setYiChengWei(cell.getContents());
								continue;
							}
							if (title.getContents().equals("第一监护人姓名")) {
								student.setYiXingMing(cell.getContents());
								continue;
							}
							if (title.getContents().equals("第一监护人工作单位")) {
								student.setYiDanWei(cell.getContents());
								continue;
							}
							if (title.getContents().equals("第一监护人职务")) {
								student.setYiZhiWu(cell.getContents());
								continue;
							}
							if (title.getContents().equals("第一监护人联系电话")) {
								student.setYiDianHua(cell.getContents());
								continue;
							}
							if (title.getContents().equals("第二监护人称谓")) {
								student.setErChengWei2(cell.getContents());
								continue;
							}
							if (title.getContents().equals("第二监护人姓名")) {
								student.setErXingMing2(cell.getContents());
								continue;
							}
							if (title.getContents().equals("第二监护人工作单位")) {
								student.setErDanWei2(cell.getContents());
								continue;
							}
							if (title.getContents().equals("第二监护人职务")) {
								student.setErZhiWu2(cell.getContents());
								continue;
							}
							if (title.getContents().equals("第二监护人联系电话")) {
								student.setErDianHua2(cell.getContents());
								continue;
							}
							if (title.getContents().equals("小学学籍号")) {
								student.setXiaoXueJiHao(cell.getContents());
								continue;
							}
							if (title.getContents().equals("户籍地教育主管部门")) {
								student.setJiaoYuZhuGuan(cell.getContents());
								continue;
							}
							if (title.getContents().equals("是否孤儿")) {
								student.setGuEr(cell.getContents());
								continue;
							}
							if (title.getContents().equals("是否残疾")) {
								student.setCanJi(cell.getContents());
								continue;
							}
							if (title.getContents().equals("是否寄宿")) {
								student.setJiSu(cell.getContents());
								continue;
							}
							if (title.getContents().equals("是否留守儿童")) {
								student.setLiuShou(cell.getContents());
								continue;
							}
							if (title.getContents().equals("验证")) {
								student.setYanZheg(cell.getContents());
								continue;
							}
							if (title.getContents().equals("房产证信息")) {
								student.setFangChanZheng(cell.getContents());
								continue;
							}
							if (title.getContents().equals("房产证产权人")) {
								student.setChanQuanRen(cell.getContents());
								continue;
							}
							/*if (title.getContents().equals("是否有身份证号")) {
								student.setYouWuSfz(cell.getContents());
								continue;
							}*/
							if (title.getContents().equals("备注")) {
								student.setBeiZhu(cell.getContents());
								continue;
							}
							if (title.getContents().equals("出生日期")
									|| title.getContents().equals("出生年月")) {
								student.setChuShengRiQi(cell.getContents());
								continue;
							}
						}
						
						if(null != student.getRuXueNian() && !"".equals(student.getRuXueNian())
								&& null != student.getShenFenZheng() && !"".equals(student.getShenFenZheng())){
							if((student.getShenFenZheng().length() == 15 
									|| student.getShenFenZheng().length() == 18)
									&& student.getRuXueNian().length() >= 4){
								String birthYear=student.getShenFenZheng().substring(6, 10);
								String ruXueNian=student.getRuXueNian().substring(0, 4);
								int age=Integer.parseInt(ruXueNian) - Integer.parseInt(birthYear);
								student.setRuXueNianLing(age);
							}
						}
						
						if(null != student.getXingMing() 
								&& !"".equals(student.getXingMing()))
							list.add(student);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		/*if (!"".equals(xueJis))
			xueJis=xueJis.substring(0, xueJis.length() - 1);*/
		return list;
	}
	
	private String getEmptyValue(String value){
		if(value.equals("无") || value.equals("否"))
			return "";
		else
			return value;
	}
}
